##################################################################################################################################
#
# Copyright (C) 2008-2014 Mind System Connection. Lda. Part of Daidze Software, created by Siqsuruq. All other rights reserved.
#
# Mind System Connection Webpage: www.msysc.org
# Daidze Software Page: www.daidze.com www.daidze.org
# E-mail: admin@msysc.org, siqsuruq@gmail.com
#
##################################################################################################################################

proc prep_search {module search_form} {
	set search_w [top_win "Search"]
	set search_w_frame [ttk::frame $search_w.top]
	set ::daidze_search [xml_wrapper new]
	pack $search_w_frame
	$::daidze_search load_main $module $search_form.xml $search_w_frame
}

proc make_search {tbl_list_var db_table {execute_after ""}} {
	set values [$::daidze_search get_values]
	# puts "****************************************\n"
	# puts $values
	# puts "****************************************\n"
	set search_tables [list $db_table]
	set initial_columns [dict keys $values]
	set search_columns [list]
	set fklist [list]
	
	set query "SELECT "
	
	foreach col $initial_columns {
		if {[string match fk_* $col] == 1} {
			lappend search_tables [set fk_table [::textutil::trim::trim $col fk_]]
			lappend search_columns "$fk_table.name AS $col"
			lappend fklist " $db_table.$col=$fk_table.id "
		} else {
			lappend search_columns "$db_table.$col AS $col"
		}
	}		

	append query "[::csv::join $search_columns]"
	append query " FROM [::csv::join $search_tables] "
	if {[llength $fklist] != 0} {
		append query " WHERE "
		append query [::csv::join $fklist AND]
	}
	
	set search_l [list]

	foreach col $initial_columns val [dict values $values] {
		if {$val ne ""} {
					puts "VAL: $val"
			if {[string match fk_* $col] == 1} {
				set fk_table [::textutil::trim::trim $col fk_]
				lappend search_l "CAST($fk_table.name as TEXT) LIKE '[string map {* %} $val]' "
			} else {
				set col_type [$::db get_columns_types $db_table $col]
				puts $col_type
				if {$col_type eq "text"} {
					lappend search_l "CAST($db_table.$col as TEXT) LIKE '[string map {* %} $val]' "
				} elseif {$col_type eq "integer" || $col_type eq "numeric" || $col_type eq "{double precision}"} {
					if {[string index $val 0] eq "<"} {
						lappend search_l "$db_table.$col < '[::textutil::tail $val]' "
					} elseif {[string index $val 0] eq ">"} {
						lappend search_l "$db_table.$col > '[::textutil::tail $val]' "
					} else {
						lappend search_l "$db_table.$col = '$val' "
					}
				} elseif {$col_type eq "DATE"} {

					set non_empty_data [remove_empty_from_list $val]
					puts "NON EMPTY: $non_empty_data"
					if {$non_empty_data ne ""} {
						if {[llength $non_empty_data] > 1} {
							lappend search_l "$db_table.$col BETWEEN '[lindex $non_empty_data 0]' AND '[lindex $non_empty_data 1]' "
						} else {
							lappend search_l "CAST($db_table.$col as TEXT) LIKE '[string map {* %} [lindex $non_empty_data 0]]' "
						}
					}
				} elseif {$col_type eq "{time without time zone}"} {
					lappend search_l "CAST($db_table.$col as TEXT) LIKE '%$val%' "
				} else {
					lappend search_l "CAST($db_table.$col as TEXT) LIKE '[string map {* %} $val]' "
				}
			}
			
		}
	}
	# set count [dict size $search_d]
	# puts "Search List: $search_l"
	

	foreach val $search_l {
		if {[llength $fklist] != 0} {
			append query " AND $val"
		} else {
			if {[lsearch $query WHERE] == "-1"} {
				append query " WHERE $val "
			} else {
				append query " AND $val"
			}
		}
	}

	
	# set default_db "host=$::db_host hostaddr=$::db_hostaddr port=$::db_port dbname=$::db_name user=$::db_user password='$::db_password'"
	# if { [catch {set connection [pg_connect -conninfo $default_db]} errmsg] } {
		# audit::audit DATABASE 1 $errmsg
		# tk_messageBox -icon error -type ok -message "[::msgcat::mc "Could not establish connection with database server:"] \n\n$errmsg"
		# exit 1
	# } else {
		# set conn $connection
	# }

	puts "\n QUERY: $query "
	
	set result [$::db execute_query $query dict]
	# if {[pg_result $res -status] == "PGRES_FATAL_ERROR"} {
		# if {$::debug == "true"} {
			# audit::audit DATABASE 1 "WEVE TRIED THIS QUERY: $query"
			# audit::audit DATABASE 1 "BUT GOT THIS ERROR: [pg_result $res -error]"
		# }
		# pg_result $res -clear
	# } else {
		# set result [pg_result $res -dict]
		# pg_result $res -clear
		# if {$::debug == "true"} {
			# audit::audit DATABASE 4 "THIS QUERY OK: $query"
		# }
# 		my llist_to_dict $result $columns
	# }
	
	puts $result

	
    set my_tablelist [dict get [$::daidze_main get_fdata] $tbl_list_var]
	set tablelist_col_list [list]
	for {set i 0} {$i < [$my_tablelist columncount]} {incr i} {
		lappend tablelist_col_list [$my_tablelist columncget $i -name]
	}
	
	set final_result [dict create]
	
	foreach res_key [dict keys $result] {
		set working_dict [dict get $result $res_key]
		set working_dict_list [dict keys $working_dict]
		
		set $res_key [dict create]
		foreach col_val $tablelist_col_list {
			# puts $col_val
			if {[lsearch $working_dict_list $col_val] != "-1"} {
				# puts "WE FOUND [dict get $working_dict $col_val]"
				dict append $res_key $col_val [dict get $working_dict $col_val]
			}
		}
		dict append final_result $res_key [set $res_key]
		# puts "LINE RESULT: "
	}

	$::daidze_main update_form [dict create $tbl_list_var $final_result]
	if {$execute_after ne ""} {

		puts [{*}$execute_after]
	}
	

	# set data [dict create]
	# foreach key [dict keys $values] value [dict values $values] {
		# dict set data $key $value
	# }

	# $::daidze_main update_form [dict create $tbl_list_var [$::db select_all_case $db_table $data]]
	# if {$execute_after ne ""} {
		# $execute_after
	# }
}